Public Class frmRanges

    Friend MyFullTableName As String

    Friend Property FullTableName() As String
        Get
            Return MyFullTableName
        End Get
        Set(ByVal Value As String)
            MyFullTableName = Value
        End Set
    End Property

    Private Sub btnShowSQL_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnShowSQL.Click
        MessageBox.Show(StringFormat(FormatSQL(BuildSQL)), "SQL String", MessageBoxButtons.OK, MessageBoxIcon.Information)
    End Sub

    Private Sub btnSelectAll_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSelectAll.Click
        btnSta.PerformClick()
        btnPCode.PerformClick()
        btnCCode.PerformClick()
        btnDate.PerformClick()
        cboOption.SelectedIndex = 0
    End Sub

    Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
        Close()
    End Sub

    Private Sub frmRanges_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Shown 'Me.Load
        Application.DoEvents()
        Me.btnShowSQL.Visible = Debugger.IsAttached
        For i As Integer = 1 To 3
            'With Project
            '    If .DataSet Is Nothing Then .DataSet = New DataSet
            '    Dim SupportTable As String = Choose(i, "Stations", "PCodes", "CCodes")
            '    If Not .ResetPDox(SupportTable) Then Exit Sub
            '    If Not .DataSet.Tables.Contains(SupportTable) Then 'not already loaded, do so now
            '        .DB.AddTable(.DataSet, SupportTable, "Select * from {0}", SupportTable)
            '    End If
            '    If i <= 2 Then
            '        Dim GroupTable As String = Choose(i, "StaGrp", "PCodeGrp")
            '        Dim GroupField As String = Choose(i, "StationGroup", "PCodeGroup")
            '        If Not .DataSet.Tables.Contains(GroupTable) Then 'not already loaded, do so now
            '            .DB.AddTable(.DataSet, GroupTable, "Select * from {0}", GroupTable)
            '            .DB.AddTable(.DataSet, GroupTable & "Names", "Select Distinct {0} from {1}", GroupField, GroupTable)
            '        End If
            '    End If
            'End With
            With CType(Choose(i, cboSta, cboPCode, cboCCode), ComboBox)
                .Items.Clear()
                .Items.Add(Choose(i, "Station ID", "PCode", "CCode"))
                .Items.Add("Names")
                If i <= 2 Then .Items.Add("Groups")
                .SelectedIndex = 0
            End With
        Next
        btnSelectAll.PerformClick()
        'Project.ResetPDox()
        cboOption.SelectedIndex = 0
        chkCCode.Checked = True
        chkDate.Checked = True
        chkPCode.Checked = True
        chkSta.Checked = True
        'chkAvail.Visible = False 'so doesnt retrieve prior value
        'GetControlValues(REGAPP, Me)
        'chkAvail.Visible = True
        cboOption.SelectedIndex = 0
        'retrieve prior selections in a specific order
        'GetControlValue(REGAPP, chkAvail, False)
        GetControlValue(REGAPP, cboSta)
        GetControlValue(REGAPP, lstSta)
        GetControlValue(REGAPP, cboPCode)
        GetControlValue(REGAPP, lstPCode)
        GetControlValue(REGAPP, cboCCode)
        GetControlValue(REGAPP, lstCCode)
        GetControlValue(REGAPP, dtStart, Now)
        GetControlValue(REGAPP, dtEnd, Now)
        dtStart.Format = System.Windows.Forms.DateTimePickerFormat.Custom
        dtStart.CustomFormat = MyDateFormat()
        dtEnd.Format = System.Windows.Forms.DateTimePickerFormat.Custom
        dtEnd.CustomFormat = MyDateFormat()

        MainForm.ProgressComplete()
    End Sub

    ''' <summary>
    ''' Refresh the list so that only the stations that exist in the currently selected browser appear
    ''' </summary>
    Private Sub chkAvail_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles chkAvail.CheckedChanged
        Try
            With Project
                Dim OpenTable As String = ActiveBrowser.FullTableName
                .ResetPDox(OpenTable)
                Dim dr As DbDataReader
                For i As Integer = 1 To 3
                    RefreshList(i)
                    'Dim lst As ListBox = Choose(i, lstSta, lstPCode, lstCCode)
                    'Dim bs As BindingSource = lst.DataSource
                    'If Me.chkAvail.Checked Then
                    '    MainForm.WriteProgress("Selecting unique values...", ProgressBarStyle.Marquee)
                    '    Dim FieldName As String = Choose(i, "Station_ID", "PCode", "CCode")
                    '    Dim FilterList As String = ""
                    '    For Each s As String In .DB.GetRecordList(OpenTable, FieldName)
                    '        FilterList &= String.Format("{0}'{1}'", IIf(FilterList = "", "", ","), s.Replace("'", ""))
                    '    Next
                    '    If FilterList <> "" Then bs.Filter = String.Format("{0} IN ({1})", FieldName, FilterList)
                    'Else
                    '    bs.RemoveFilter()
                    'End If
                    'lst.DataSource = bs
                Next i
                If chkAvail.Checked Then
                    dr = .DB.ExecuteReader("SELECT MIN(Date_Time) as MinDate, MAX(Date_Time) as MaxDate FROM {0}", OpenTable)
                    If dr Is Nothing Then Exit Sub
                    If dr IsNot Nothing AndAlso dr.Read Then
                        Me.dtStart.Value = TestNull(dr.GetValue(0), dtStart.MinDate)
                        Me.dtEnd.Value = TestNull(dr.GetValue(1), dtStart.MaxDate)
                    End If
                    dr.Close()
                Else
                    dtStart.Value = dtStart.MinDate
                    dtEnd.Value = dtEnd.MaxDate
                End If
            End With
            Me.btnSelectAll.PerformClick()
        Catch ex As Exception
            ErrorMsg(, ex)
        Finally
            MainForm.ProgressComplete()
        End Try
    End Sub

    Private Sub cboSta_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles cboSta.SelectedIndexChanged, cboPCode.SelectedIndexChanged, cboCCode.SelectedIndexChanged
        RefreshList(WhichCntl(sender))
        'Dim bs As New BindingSource()
        'With bs
        '    Select Case CType(Choose(WhichCntl(sender), cboSta, cboPCode, cboCCode), ComboBox).SelectedIndex
        '        Case 0 : .DataSource = Project.DataSet.Tables(Choose(WhichCntl(sender), "Stations", "PCodes", "CCodes"))
        '        Case 1 : .DataSource = Project.DataSet.Tables(Choose(WhichCntl(sender), "Stations", "PCodes", "CCodes"))
        '        Case 2 : .DataSource = Project.DB.GetRecordList(Choose(WhichCntl(sender), "StaGrp", "PCodeGrp"), Choose(WhichCntl(sender), "StationGroup", "PCodeGroup"))
        '    End Select
        'End With
        'With CType(Choose(WhichCntl(sender), lstSta, lstPCode, lstCCode), ListBox)
        '    .Sorted = True
        '    .DataSource = bs
        '    Select Case CType(Choose(WhichCntl(sender), cboSta, cboPCode, cboCCode), ComboBox).SelectedIndex
        '        Case 0 : .DisplayMember = Choose(WhichCntl(sender), "Station_ID", "PCode", "CCode")
        '        Case 1 : .DisplayMember = Choose(WhichCntl(sender), "Station_Name", "Anal_Name", "CCode_Name")
        '        Case 2 '.DisplayMember = Choose(WhichCntl(sender), "StationGroup", "PCodeGroup", "")
        '    End Select
        '    .SelectedItems.Clear()
        'End With
    End Sub

    Private Sub RefreshList(ByVal ControlIndex As Integer)
        Me.UseWaitCursor = True
        Application.DoEvents()
        Dim lst As Generic.List(Of String) = Nothing
        Dim cboIndex As Integer = CType(Choose(ControlIndex, cboSta, cboPCode, cboCCode), ComboBox).SelectedIndex '0=ID; 1=Name; 2=Group
        Dim tblName As String = ""
        Dim fldName As String = ""
        Dim whereClause As String = ""

        If chkAvail.Checked Then
            Select Case cboIndex
                Case 0
                    fldName = Choose(ControlIndex, "Station_ID", "PCode", "CCode")
                    If cboIndex = 0 Then tblName = ActiveBrowser.FullTableName Else tblName = Choose(ControlIndex, "Stations", "PCodes", "CCodes")
                Case 1
                    fldName = Choose(ControlIndex, "Station_Name", "Anal_Name", "CCode_Name")
                    Dim fldName2 As String = Choose(ControlIndex, "Station_ID", "PCode", "CCode")
                    tblName = Choose(ControlIndex, "Stations", "PCodes", "CCodes")
                    whereClause = String.Format("WHERE {0} IN (SELECT DISTINCT {0} FROM {1})", fldName2, ActiveBrowser.FullTableName)
                Case 2
                    fldName = Choose(ControlIndex, "StationGroup", "PCodeGroup")
                    tblName = Choose(ControlIndex, "StaGrp", "PCodeGrp")
            End Select
        Else
            Select cboIndex
                Case 0
                    fldName = Choose(ControlIndex, "Station_ID", "PCode", "CCode")
                    tblName = Choose(ControlIndex, "Stations", "PCodes", "CCodes")
                Case 1
                    fldName = Choose(ControlIndex, "Station_Name", "Anal_Name", "CCode_Name")
                    tblName = Choose(ControlIndex, "Stations", "PCodes", "CCodes")
                Case 2
                    fldName = Choose(ControlIndex, "StationGroup", "PCodeGroup")
                    tblName = Choose(ControlIndex, "StaGrp", "PCodeGrp")
            End Select
        End If

        lst = Project.DB.GetRecordList(tblName, fldName, whereClause)

        'Select Case CType(Choose(ControlIndex, cboSta, cboPCode, cboCCode), ComboBox).SelectedIndex
        '    Case 0 : lst = Project.DB.GetRecordList(IIf(chkAvail.Checked, ActiveBrowser.FullTableName, Choose(ControlIndex, "Stations", "PCodes", "CCodes")), Choose(ControlIndex, "Station_ID", "PCode", "CCode"))
        '    Case 1 : lst = Project.DB.GetRecordList(IIf(chkAvail.Checked, ActiveBrowser.FullTableName, Choose(ControlIndex, "Stations", "PCodes", "CCodes")), Choose(ControlIndex, "Station_Name", "Anal_Name", "CCode_Name"))
        '    Case 2 : lst = Project.DB.GetRecordList(Choose(ControlIndex, "StaGrp", "PCodeGrp"), Choose(ControlIndex, "StationGroup", "PCodeGroup"))
        'End Select

        With CType(Choose(ControlIndex, lstSta, lstPCode, lstCCode), ListBox)
            .Items.Clear()
            .Items.AddRange(lst.ToArray)
        End With
        Me.UseWaitCursor = False
        Application.DoEvents()
    End Sub

    Private Sub btnSta_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSta.Click, btnPCode.Click, btnCCode.Click
        'clear all selections, set checkbox
        With CType(Choose(WhichCntl(sender), lstSta, lstPCode, lstCCode), ListBox)
            .SelectedItems.Clear()
            .TopIndex = 0
        End With
    End Sub

    Private Sub lstSta_MouseDoubleClick(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles lstSta.MouseDoubleClick, lstPCode.MouseDoubleClick, lstCCode.MouseDoubleClick
        Me.btnExecute.PerformClick()
    End Sub

    Private Sub lstSta_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles lstSta.SelectedIndexChanged, lstPCode.SelectedIndexChanged, lstCCode.SelectedIndexChanged
        'if no items in listbox are selected, clear checkbox
        CType(Choose(WhichCntl(sender), chkSta, chkPCode, chkCCode, chkDate), CheckBox).Checked = CType(Choose(WhichCntl(sender), lstSta, lstPCode, lstCCode), ListBox).SelectedItems.Count = 0
        With CType(Choose(WhichCntl(sender), grpSta, grpPcode, grpCCode), GroupBox)
            .Text = Choose(WhichCntl(sender), "Stations", "Parameter", "Compositing Codes")
            Dim num As Integer = CType(Choose(WhichCntl(sender), lstSta, lstPCode, lstCCode), ListBox).SelectedItems.Count
            If num > 0 Then .Text &= String.Format(" ({0} selected)", num)
            .Text &= ":"
        End With
    End Sub

    Private Function WhichCntl(ByVal sender As Object) As Integer
        'figure out which control was acted upon, return integer so can use Choose stmt
        Dim CntlName As String = CType(sender, Control).Name
        If CntlName.EndsWith("Sta") Then
            Return 1
        ElseIf CntlName.EndsWith("PCode") Then
            Return 2
        ElseIf CntlName.EndsWith("CCode") Then
            Return 3
        Else
            Return 4
        End If
    End Function

    Private Sub btnDate_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnDate.Click
        With Project
            If chkAvail.Checked Then
                Dim dr As DbDataReader = .DB.ExecuteReader("SELECT MIN(Date_Time) as MinDate, MAX(Date_Time) as MaxDate FROM {0}", ActiveBrowser.FullTableName)
                If dr Is Nothing Then Exit Sub
                If dr IsNot Nothing AndAlso dr.Read Then
                    dtStart.Value = dtStart.MinDate
                    If Not IsDBNull(dr.GetValue(0)) AndAlso IsDate(dr.GetValue(0)) AndAlso CDate(dr.GetValue(0)) >= dtStart.MinDate Then dtStart.Value = dr.GetValue(0)
                    dtEnd.Value = dtEnd.MaxDate
                    If Not IsDBNull(dr.GetValue(1)) AndAlso IsDate(dr.GetValue(1)) AndAlso CDate(dr.GetValue(1)) <= dtEnd.MaxDate Then dtEnd.Value = dr.GetValue(1)
                    'Me.dtStart.Value = TestNull(dr.GetValue(0), dtStart.MinDate)
                    'Me.dtEnd.Value = TestNull(dr.GetValue(1), dtStart.MaxDate)
                End If
                dr.Close()
            Else
                dtStart.Value = dtStart.MinDate
                dtEnd.Value = dtEnd.MaxDate
            End If
        End With
        Me.chkDate.Checked = True

        'With dtStart
        '    .Value = .MinDate
        'End With
        'With dtEnd
        '    .Value = .MaxDate
        'End With
    End Sub

    Private Sub dtStart_ValueChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles dtStart.ValueChanged, dtEnd.ValueChanged
        Me.chkDate.Checked = dtStart.Value = dtStart.MinDate And dtEnd.Value = dtEnd.MaxDate
    End Sub

    Friend Function BuildSQL() As String

        Dim WhereClause As String

        WhereClause = ""

        For i As Integer = 1 To 3
            With CType(Choose(i, lstSta, lstPCode, lstCCode), ListBox)
                If .SelectedItems.Count > 0 Then
                    WhereClause &= IIf(WhereClause = "", "", " AND ")
                    Dim List As String = ""
                    Dim SelIndex As Integer = CType(Choose(i, cboSta, cboPCode, cboCCode), ComboBox).SelectedIndex
                    Select Case SelIndex
                        Case 0 'ID
                            For Each s As String In .SelectedItems
                                List &= IIf(List = "", "", ",") & String.Format("'{0}'", s)
                            Next
                        Case 1 'name
                            'selected from group name, gather from support table (not ccodes)
                            For Each name As String In .SelectedItems
                                Dim tbl As String = Choose(i, "Stations", "PCodes", "CCodes")
                                Dim fld As String = Choose(i, "Station_Name", "Anal_Name", "CCode_Name")
                                Dim key As String = Choose(i, "Station_ID", "PCode", "CCode")
                                For Each item As String In Project.DB.GetRecordList(tbl, key, String.Format("WHERE {0}='{1}'", fld, name))
                                    List &= IIf(List = "", "", ",") & String.Format("'{0}'", item)
                                Next
                            Next
                        Case 2 'group
                            'selected from group name, gather from support table (not ccodes)
                            For Each grpName As String In .SelectedItems
                                Dim tbl As String = Choose(i, "StaGrp", "PCodeGrp")
                                Dim grp As String = Choose(i, "StationGroup", "PCodeGroup")
                                Dim key As String = Choose(i, "Station_ID", "PCode")
                                For Each item As String In Project.DB.GetRecordList(tbl, key, String.Format("WHERE {0}='{1}'", grp, grpName))
                                    List &= IIf(List = "", "", ",") & String.Format("'{0}'", item)
                                Next
                            Next
                    End Select
                    WhereClause &= String.Format("{0} IN ({1})", Choose(i, "Station_ID", "PCode", "CCode"), List)
                End If
            End With
        Next
        If dtStart.Value <> dtStart.MinDate Or dtEnd.Value <> dtEnd.MaxDate Then
            WhereClause &= IIf(WhereClause = "", "", " AND ")
            WhereClause &= String.Format("Date_Time BETWEEN {0} and {1}", Project.DB.DateTime(dtStart.Value), Project.DB.DateTime(dtEnd.Value.AddDays(1).AddMinutes(-1)))
        End If

        If WhereClause <> "" Then WhereClause = " WHERE " & WhereClause

        Return MainForm.SelectBuildQuery(Me.cboOption.SelectedIndex, WhereClause)
    End Function

    Private Sub btnExecute_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnExecute.Click
        Try
            If Not Project.ResetPDox(ActiveBrowser.TableName) Then Exit Sub
            MainForm.SelectData(BuildSQL, False)
            'SaveControlValues(REGAPP, Me)
            'save prior selections in a specific order
            SaveControlValue(REGAPP, chkAvail)
            SaveControlValue(REGAPP, cboSta)
            SaveControlValue(REGAPP, lstSta)
            SaveControlValue(REGAPP, cboPCode)
            SaveControlValue(REGAPP, lstPCode)
            SaveControlValue(REGAPP, cboCCode)
            SaveControlValue(REGAPP, lstCCode)
            SaveControlValue(REGAPP, dtStart)
            SaveControlValue(REGAPP, dtEnd)
        Catch ex As Exception
            ErrorMsg(, ex)
        End Try
    End Sub

    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        frmInfoMsg.ShowInfoMsg("Parameter Queries", "You are about to save this query so it can be reused at a later time.\n\nNote that WRDB can also embed parameters in your queries to make it more convenient to reuse them. For each named parameter you place into your query enclosed in brackets (e.g., [PCODE PROMPT]), a prompt will appear when you rerun the query which allows you to specify the desired value at that time.\n\nAlthough you cannot embed parameters using this query form, you can edit the SQL file and manually insert the parameters if you desire.")
        With New SaveFileDialog
            .AddExtension = True
            .CheckPathExists = True
            .SupportMultiDottedExtensions = False
            .DefaultExt = "SQL"
            .Filter = "Saved SQL Query File (*.sql)|*.sql"
            .InitialDirectory = TestNull(Project.SQLDir, AppDataPath)
            .SupportMultiDottedExtensions = False
            If .ShowDialog(Me) = Windows.Forms.DialogResult.OK Then
                Dim sw As New IO.StreamWriter(.FileName, False)
                sw.Write(BuildSQL)
                sw.Close()
                sw.Dispose()
                sw = Nothing
            End If
        End With
    End Sub

    Private Sub chkPCode_CheckStateChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles chkPCode.CheckStateChanged

    End Sub

    Private Sub btnGIS_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnGIS.Click
        Try
            Dim lst As New Generic.List(Of String)
            If IsMWForm Then
                'lst = CType(MainForm, frmMainMW).SelectedStations
            Else
                MainForm.CheckGisData()
                With New WRDB.GIS.frmGIS(Project, ActiveTableName, clsProject.TempData, GIS.frmGIS.enumMode.SelectStations, MainWRDB.WRDBStationTypes, Project.GisBasemapStations)
                    If .ShowDialog(Me) = Windows.Forms.DialogResult.OK Then
                        lst = .SelectedStations
                    End If
                    .Dispose()
                End With
            End If
            For Each s As String In lst
                For i As Integer = 0 To lstSta.Items.Count - 1
                    If lstSta.Items(i) = s Then lstSta.SelectedItems.Add(lstSta.Items(i))
                Next
            Next
        Catch ex As Exception
            ErrorMsg(, ex)
        End Try
    End Sub
End Class